-- EXEC SP_WAFERS_SORTED_DETAILS_REPORT '0','3','2005','0'
ALTER PROC SP_WAFERS_SORTED_DETAILS_REPORT

@PROCESSSTEP_ID VARCHAR(50),
@CURRENT_QUARTER VARCHAR(4),
@CURRENT_YEAR VARCHAR(4),
@DEFAULT_PROMPT VARCHAR(20)

AS

DECLARE @TEMPPROCESSSTEP_ID INT 
DECLARE @PART_NUMBER VARCHAR(50)
DECLARE @PS_TYPE VARCHAR(50)
DECLARE @LOT_ID INT
DECLARE @WAFER_ID INT
DECLARE @GOOD_DICE INT
DECLARE @MRB_LIMIT BIT
DECLARE @LOT_NUMBER VARCHAR(50)
DECLARE @TIME_STAMP DATETIME
DECLARE @ACTYEAR INT
DECLARE @ACTMONTH INT
DECLARE @QTR_START INT
DECLARE @QTR_END INT
DECLARE @TOTAL_WAFERS_COUNT INT
DECLARE @TOTAL_WAFER_OUT INT
DECLARE @WAFERS_IN_MRB INT
DECLARE @GOOD_WAFERS_IN_MRB INT
DECLARE @WAFERS_OUT_MRB INT
DECLARE @GOOD_WAFERS_OUT_MRB INT
DECLARE @RMA_LIMIT INT
DECLARE @BASE_PRODUCT VARCHAR(50)

DECLARE @RESULTS TABLE ( PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50),PROCESSSTEP_ID BIGINT,PS_TYPE VARCHAR(50),LOT_ID INT,WAFER_ID INT,
			 GOOD_DICE INT,MRB_LIMIT BIT,LOT_NUMBER VARCHAR(50),TIME_STAMP DATETIME,ACTYEAR INT,ACTMONTH INT,QTR_START INT,QTR_END INT, RMA_LIMIT INT)

DECLARE @RESULTS_TABLE TABLE ( PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50),TOTAL_WAFERS_COUNT INT, TOTAL_WAFER_OUT INT, WAFERS_COUNT_IN_MRB INT, WAFER_OUT_IN_MRB INT,
			 WAFERS_COUNT_OUT_MRB INT, WAFER_OUT_MRB INT, QTR_WAFERS_COUNT INT, QTR_WAFER_OUT INT, QTR_WAFERS_COUNT_IN_MRB INT, QTR_WAFER_OUT_IN_MRB INT, QTR_WAFERS_COUNT_OUT_MRB INT, QTR_WAFER_OUT_MRB INT)



DECLARE @MAINCURSOR CURSOR
DECLARE @RESULTCURSOSR CURSOR

BEGIN
	
	IF(@PROCESSSTEP_ID = '0')
	BEGIN
		SET @MAINCURSOR = CURSOR FOR SELECT P.PART_NUMBER,( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			 CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
			 END ),IB.LOT_ID,IB.WAFER_ID,IB.GOOD_DICE,IB.MRB_LIMIT,IB.RMA_LIMIT,LT.LOT_ID,LT.LOT_NUMBER,LT.PROCESSSTEP_ID,SLH.TIMESTAMP
		FROM INFORMATIVE_BINNING IB,LOT_TABLE LT,SORT_LOT_HISTORY SLH,PRODUCT P,PROCESS_STEP PS
		WHERE IB.LOT_ID = LT.LOT_ID AND IB.LOT_ID = SLH.LOT_ID AND
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND
		P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'SORT' AND ( P.P_TYPE IS NULL OR P.P_TYPE = 'BOARD' OR P.P_TYPE = 'KIT')
		AND SLH.TIMESTAMP = ( SELECT MAX(SLH1.TIMESTAMP) FROM SORT_LOT_HISTORY SLH1 WHERE SLH1.LOT_ID = SLH.LOT_ID  )
	END
	ELSE
	BEGIN
		SET @MAINCURSOR = CURSOR FOR SELECT P.PART_NUMBER,( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			 CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
			 END ),IB.LOT_ID,IB.WAFER_ID,IB.GOOD_DICE,IB.MRB_LIMIT,IB.RMA_LIMIT,LT.LOT_ID,LT.LOT_NUMBER,LT.PROCESSSTEP_ID,SLH.TIMESTAMP
		FROM INFORMATIVE_BINNING IB,LOT_TABLE LT,SORT_LOT_HISTORY SLH,PRODUCT P,PROCESS_STEP PS
		WHERE IB.LOT_ID = LT.LOT_ID AND IB.LOT_ID = SLH.LOT_ID AND
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND
		P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'SORT' AND ( P.P_TYPE IS NULL OR P.P_TYPE = 'BOARD' OR P.P_TYPE = 'KIT')
		AND SLH.TIMESTAMP = ( SELECT MAX(SLH1.TIMESTAMP) FROM SORT_LOT_HISTORY SLH1 WHERE SLH1.LOT_ID = SLH.LOT_ID )
		AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
	END	

	OPEN @MAINCURSOR 
	FETCH NEXT FROM @MAINCURSOR INTO @PART_NUMBER,@BASE_PRODUCT,@LOT_ID,@WAFER_ID,@GOOD_DICE,@MRB_LIMIT,@RMA_LIMIT,@LOT_ID,@LOT_NUMBER,@TEMPPROCESSSTEP_ID,@TIME_STAMP
	WHILE @@FETCH_STATUS = 0
	BEGIN
						
		SET @ACTYEAR = DATEPART(YYYY, @TIME_STAMP)
		SET @ACTMONTH = DATEPART(MM, @TIME_STAMP)
				
		IF (@ACTMONTH  >=1 AND @ACTMONTH <=3)
		BEGIN
			SET @QTR_START = 1
			SET @QTR_END =3
		END
		
		ELSE IF (@ACTMONTH >=4 AND @ACTMONTH <=6)
		BEGIN
			SET @QTR_START = 2
			SET @QTR_END =6
		END
		
		ELSE IF (@ACTMONTH  >=7 AND @ACTMONTH <=9)
		BEGIN
			SET @QTR_START = 3
			SET @QTR_END =9
		END						
		
		ELSE IF (@ACTMONTH >=10 AND @ACTMONTH <=12)
		BEGIN
			SET @QTR_START = 4
			SET @QTR_END =12
			
		END

		INSERT INTO @RESULTS (PART_NUMBER,BASE_PRODUCT,PROCESSSTEP_ID,PS_TYPE,LOT_ID,WAFER_ID,GOOD_DICE,MRB_LIMIT,LOT_NUMBER,TIME_STAMP,ACTYEAR,ACTMONTH,QTR_START,QTR_END,RMA_LIMIT)
			VALUES (@PART_NUMBER,@BASE_PRODUCT,@TEMPPROCESSSTEP_ID,@PS_TYPE,@LOT_ID,@WAFER_ID,@GOOD_DICE,@MRB_LIMIT,@LOT_NUMBER,@TIME_STAMP,@ACTYEAR,@ACTMONTH,@QTR_START,@QTR_END,@RMA_LIMIT)
			SET @PART_NUMBER = NULL
			SET @TEMPPROCESSSTEP_ID = NULL
			SET @PS_TYPE = NULL
			SET @LOT_ID = NULL
			SET @WAFER_ID = NULL
			SET @GOOD_DICE = NULL
			SET @MRB_LIMIT = NULL
			SET @LOT_NUMBER = NULL
			SET @TIME_STAMP = NULL
			SET @ACTYEAR = NULL
			SET @ACTMONTH = NULL
			SET @QTR_START = NULL
			SET @QTR_END = NULL
			SET @RMA_LIMIT = NULL
			SET @BASE_PRODUCT = NULL

	FETCH NEXT FROM @MAINCURSOR INTO @PART_NUMBER,@BASE_PRODUCT,@LOT_ID,@WAFER_ID,@GOOD_DICE,@MRB_LIMIT,@RMA_LIMIT,@LOT_ID,@LOT_NUMBER,@TEMPPROCESSSTEP_ID,@TIME_STAMP
	END
	CLOSE @MAINCURSOR
	DEALLOCATE @MAINCURSOR

	--select * from @results where lot_id = 2399
	INSERT INTO @RESULTS_TABLE 
	 SELECT R1.PART_NUMBER,R1.BASE_PRODUCT, COUNT(R1.WAFER_ID) AS TOTAL_WAFERS_COUNT, SUM(R1.GOOD_DICE) AS TOTAL_WAFER_OUT,
	 ( SELECT COUNT(R2.WAFER_ID) FROM @RESULTS R2 WHERE R2.PART_NUMBER = R1.PART_NUMBER AND (R2.MRB_LIMIT = 1 AND R2.RMA_LIMIT = 1) and (r2.time_stamp = r1.time_stamp) )  AS WAFERS_COUNT_IN_MRB,
	 ( SELECT isNull(SUM(R3.GOOD_DICE),0) FROM @RESULTS R3 WHERE R3.PART_NUMBER = R1.PART_NUMBER AND (R3.MRB_LIMIT = 1 AND R3.RMA_LIMIT = 1) and (r3.time_stamp = r1.time_stamp) )AS WAFER_OUT_IN_MRB,
	 ( SELECT COUNT(R4.WAFER_ID) FROM @RESULTS R4 WHERE R4.PART_NUMBER = R1.PART_NUMBER AND (R4.MRB_LIMIT = 0 OR R4.RMA_LIMIT = 0) and (r4.time_stamp = r1.time_stamp) ) AS WAFERS_COUNT_OUT_MRB,
	 ( SELECT isNull(SUM(R5.GOOD_DICE),0) FROM @RESULTS R5 WHERE R5.PART_NUMBER = R1.PART_NUMBER AND (R5.MRB_LIMIT = 0 OR R5.RMA_LIMIT = 0) and (r5.time_stamp = r1.time_stamp) )AS WAFER_OUT_MRB,
	
	 ( SELECT COUNT(R6.WAFER_ID) FROM @RESULTS R6 WHERE R6.PART_NUMBER = R1.PART_NUMBER AND R6.QTR_START = @CURRENT_QUARTER AND R6.ACTYEAR = @CURRENT_YEAR and (r6.time_stamp = r1.time_stamp)) AS QTR_WAFERS_COUNT, 
	 ( SELECT isNull(SUM(R7.GOOD_DICE),0)  FROM @RESULTS R7 WHERE R7.PART_NUMBER = R1.PART_NUMBER AND R7.QTR_START = @CURRENT_QUARTER AND R7.ACTYEAR = @CURRENT_YEAR and (r7.time_stamp =r1.time_stamp)) AS QTR_WAFER_OUT,
	 ( SELECT COUNT(R8.WAFER_ID) FROM @RESULTS R8 WHERE R8.PART_NUMBER = R1.PART_NUMBER AND (R8.MRB_LIMIT = 1 AND R8.RMA_LIMIT = 1 )AND R8.QTR_START = @CURRENT_QUARTER AND R8.ACTYEAR = @CURRENT_YEAR and (r8.time_stamp = r1.time_stamp)) AS QTR_WAFERS_COUNT_IN_MRB,
	 ( SELECT isNull(SUM(R9.GOOD_DICE),0) FROM @RESULTS R9 WHERE R9.PART_NUMBER = R1.PART_NUMBER AND (R9.MRB_LIMIT = 1 AND R9.RMA_LIMIT = 1)AND R9.QTR_START = @CURRENT_QUARTER AND R9.ACTYEAR = @CURRENT_YEAR and (r9.time_stamp = r1.time_stamp)) AS QTR_WAFER_OUT_IN_MRB,
	 ( SELECT COUNT(R10.WAFER_ID) FROM @RESULTS R10 WHERE R10.PART_NUMBER = R1.PART_NUMBER AND (R10.MRB_LIMIT = 0 OR R10.RMA_LIMIT = 0) AND R10.QTR_START = @CURRENT_QUARTER AND R10.ACTYEAR = @CURRENT_YEAR and (r10.time_stamp =r1.time_stamp)) AS QTR_WAFERS_COUNT_OUT_MRB,
	 ( SELECT isNull(SUM(R11.GOOD_DICE),0) FROM @RESULTS R11 WHERE R11.PART_NUMBER = R1.PART_NUMBER AND (R11.MRB_LIMIT = 0 OR R11.RMA_LIMIT = 0) AND R11.QTR_START = @CURRENT_QUARTER AND R11.ACTYEAR = @CURRENT_YEAR and (r11.time_stamp = r1.time_stamp)) AS QTR_WAFER_OUT_MRB

	FROM @RESULTS R1 /*where lot_id = 2399*/ GROUP BY R1.PART_NUMBER,R1.BASE_PRODUCT, r1.time_stamp

	SELECT PART_NUMBER,BASE_PRODUCT, SUM(TOTAL_WAFERS_COUNT) AS TOTAL_WAFERS_COUNT, SUM(TOTAL_WAFER_OUT) AS TOTAL_WAFER_OUT, SUM(WAFERS_COUNT_IN_MRB) AS WAFERS_COUNT_IN_MRB, SUM(WAFER_OUT_IN_MRB) AS WAFER_OUT_IN_MRB, SUM(WAFERS_COUNT_OUT_MRB) AS WAFERS_COUNT_OUT_MRB, SUM(WAFER_OUT_MRB) AS WAFER_OUT_MRB, SUM(QTR_WAFERS_COUNT) AS QTR_WAFERS_COUNT, SUM(QTR_WAFER_OUT) AS QTR_WAFER_OUT, SUM(QTR_WAFERS_COUNT_IN_MRB) AS QTR_WAFERS_COUNT_IN_MRB, SUM(QTR_WAFER_OUT_IN_MRB)AS QTR_WAFER_OUT_IN_MRB, SUM(QTR_WAFERS_COUNT_OUT_MRB) AS QTR_WAFERS_COUNT_OUT_MRB, SUM(QTR_WAFER_OUT_MRB) AS QTR_WAFER_OUT_MRB
	FROM @RESULTS_TABLE
	GROUP BY PART_NUMBER,BASE_PRODUCT

END
